import webbrowser
from flask import Flask, jsonify, request, render_template
import pymysql
from pymysql.cursors import DictCursor
import os
from datetime import datetime

app = Flask(__name__)

# 数据库配置
db_config = {
    'host': 'localhost',
    'port': 3306,
    'user': 'root',
    'password': "3185913488Ll.",
    'database': 'bank_system',
    'charset': 'utf8mb4',
    'cursorclass': DictCursor
}


def get_db_connection():
    return pymysql.connect(**db_config)


@app.route('/')
def index():
    return render_template('index.html')


# 获取表数据
@app.route('/api/tables/<table_name>', methods=['GET'])
def get_table_data(table_name):
    valid_tables = ['userinfo', 'cardinfo', 'tradeinfo', 'audit_log', 'deposit']
    if table_name not in valid_tables:
        return jsonify({'error': 'Invalid table name'}), 400

    try:
        conn = get_db_connection()
        with conn.cursor() as cursor:
            cursor.execute(f"SELECT * FROM {table_name}")
            data = cursor.fetchall()
        conn.close()
        return jsonify(data)
    except Exception as e:
        return jsonify({'error': str(e)}), 500


# 添加新记录
# ... 前面的代码保持不变 ...

# 添加新记录 - 修复银行卡添加功能
@app.route('/api/tables/<table_name>', methods=['POST'])
def add_record(table_name):
    valid_tables = ['userinfo', 'cardinfo']
    if table_name not in valid_tables:
        return jsonify({'error': 'Table not supported for adding records'}), 400

    data = request.get_json()
    if not data:
        return jsonify({'error': 'No data provided'}), 400

    try:
        conn = get_db_connection()
        with conn.cursor() as cursor:
            # 用户添加逻辑
            if table_name == 'userinfo':
                columns = ', '.join(data.keys())
                placeholders = ', '.join(['%s'] * len(data))
                sql = f"INSERT INTO {table_name} ({columns}) VALUES ({placeholders})"
                cursor.execute(sql, list(data.values()))
                conn.commit()
                return jsonify({'success': True, 'message': '用户添加成功'})

            # 银行卡添加逻辑 - 检查用户ID是否存在
            elif table_name == 'cardinfo':
                # 检查用户ID是否存在
                cursor.execute("SELECT COUNT(*) AS cnt FROM userinfo WHERE customerID = %s", (data['customerID'],))
                result = cursor.fetchone()
                if result['cnt'] == 0:
                    return jsonify({'error': '用户ID不存在'}), 400

                # 设置默认值
                data['openDate'] = datetime.now().strftime('%Y-%m-%d %H:%M:%S')
                data['balance'] = data['openMoney']  # 开户金额等于余额
                data['IsReportLoss'] = '0'  # 默认未挂失

                # 生成银行卡号
                cursor.execute("SELECT MAX(cardID) AS max_card FROM cardinfo")
                max_card = cursor.fetchone()['max_card']
                if max_card:
                    next_id = int(max_card[8:]) + 1
                else:
                    next_id = 1
                card_id = f"10103576{str(next_id).zfill(8)}"

                # 插入数据
                columns = ['cardID', 'curID', 'savingID', 'openDate', 'openMoney', 'balance', 'pass', 'IsReportLoss',
                           'customerID']
                values = [card_id, data['curID'], data['savingID'], data['openDate'],
                          data['openMoney'], data['balance'], data['pass'],
                          data['IsReportLoss'], data['customerID']]

                sql = f"INSERT INTO cardinfo ({', '.join(columns)}) VALUES ({', '.join(['%s'] * len(columns))})"
                cursor.execute(sql, values)
                conn.commit()
                return jsonify({'success': True, 'message': '银行卡添加成功'})

    except Exception as e:
        conn.rollback()
        return jsonify({'error': str(e)}), 500
    finally:
        if conn:
            conn.close()


# 切换银行卡挂失状态
@app.route('/api/tables/cardinfo/<cardID>/toggle-loss', methods=['PUT'])
def toggle_card_loss(cardID):
    try:
        conn = get_db_connection()
        with conn.cursor() as cursor:
            # 获取当前状态
            cursor.execute("SELECT IsReportLoss FROM cardinfo WHERE cardID = %s", (cardID,))
            card = cursor.fetchone()
            if not card:
                return jsonify({'error': '银行卡不存在'}), 404

            new_status = '1' if card['IsReportLoss'] == '0' else '0'

            # 更新状态
            cursor.execute("UPDATE cardinfo SET IsReportLoss = %s WHERE cardID = %s",
                           (new_status, cardID))
            conn.commit()

            # 记录审计日志
            operation = '挂失' if new_status == '1' else '解挂'
            cursor.execute("""
                INSERT INTO audit_log (card_id, operation_time, operation_type, remark)
                VALUES (%s, NOW(), %s, %s)
            """, (cardID, operation, f'银行卡{operation}操作'))

            conn.commit()
            return jsonify({'success': True, 'new_status': new_status})
    except Exception as e:
        conn.rollback()
        return jsonify({'error': str(e)}), 500
    finally:
        if conn:
            conn.close()


# ... 后面的代码保持不变 ...


# 更新记录 - 修复后的版本
    # 更新记录 - 修复后的版本
@app.route('/api/tables/<table_name>/<int:id>', methods=['PUT'])  # 添加 int: 类型转换
def update_record(table_name, id):

    # ... 其余代码保持不变 ...
    valid_tables = ['userinfo', 'cardinfo']
    if table_name not in valid_tables:
        return jsonify({'error': 'Table not supported for updating records'}), 400

    data = request.get_json()
    if not data:
        return jsonify({'error': 'No data provided'}), 400

    try:
        conn = get_db_connection()
        with conn.cursor() as cursor:
            # 处理特殊字段
            if table_name == 'cardinfo':
                # 确保余额不能为负数
                if 'balance' in data and data['balance'] < 0:
                    return jsonify({'error': '余额不能为负数'}), 400

                # 处理日期格式
                if 'openDate' in data and data['openDate']:
                    data['openDate'] = datetime.strptime(data['openDate'], '%Y-%m-%dT%H:%M:%S')

            # 构建更新语句
            set_clause = ', '.join([f"{key} = %s" for key in data.keys()])
            values = list(data.values())
            values.append(id)

            # 根据表确定主键字段
            pk_field = 'customerID' if table_name == 'userinfo' else 'cardID'

            sql = f"UPDATE {table_name} SET {set_clause} WHERE {pk_field} = %s"
            cursor.execute(sql, values)
            conn.commit()
        conn.close()
        return jsonify({'success': True, 'message': 'Record updated successfully'})
    except Exception as e:
        conn.rollback()
        return jsonify({'error': str(e)}), 500


# 删除记录
@app.route('/api/tables/<table_name>/<id>', methods=['DELETE'])
def delete_record(table_name, id):
    valid_tables = ['userinfo', 'cardinfo']
    if table_name not in valid_tables:
        return jsonify({'error': 'Table not supported for deletion'}), 400

    try:
        conn = get_db_connection()
        with conn.cursor() as cursor:
            # 根据表确定主键字段
            pk_field = 'customerID' if table_name == 'userinfo' else 'cardID'

            # 对于用户表，需要先删除关联的银行卡
            if table_name == 'userinfo':
                cursor.execute("DELETE FROM cardinfo WHERE customerID = %s", (id,))

            sql = f"DELETE FROM {table_name} WHERE {pk_field} = %s"
            cursor.execute(sql, (id,))
            conn.commit()
        conn.close()
        return jsonify({'success': True, 'message': 'Record deleted successfully'})
    except Exception as e:
        conn.rollback()
        return jsonify({'error': str(e)}), 500


# 执行SQL查询
@app.route('/api/query', methods=['POST'])
def execute_query():
    data = request.get_json()
    sql = data.get('sql')
    if not sql:
        return jsonify({'error': 'No SQL query provided'}), 400

    try:
        conn = get_db_connection()
        with conn.cursor() as cursor:
            cursor.execute(sql)

            # 如果是SELECT查询，返回结果
            if sql.strip().lower().startswith('select'):
                result = cursor.fetchall()
                return jsonify(result)
            else:
                conn.commit()
                return jsonify({'success': True, 'message': 'Query executed successfully'})
    except Exception as e:
        conn.rollback()
        return jsonify({'error': str(e)}), 500
    finally:
        conn.close()


if __name__ == '__main__':
    webbrowser.open('http://127.0.0.1:5001')  # 假设app.py运行在5001端口
    app.run(debug=False, port=5001)